package com.matt.repository.impl;

import com.matt.foundation.vo.WeekPaperAdviserVo;
import com.matt.foundation.vo.WeekPaperAreaVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.WeekPaperNativeRepository;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on $date$
 * *******************************
 */
@Service
public class WeekPaperNativeRepositoryImpl extends BaseNativeSqlRepository implements WeekPaperNativeRepository {


    @Override
    public WeekPaperAdviserVo getTeacherWeekData(String areaId, String staffId,String date) {
        String querySql = String.format("SELECT a.newLeadsNum as newLeadsNum,b.unFollowLeadsNum as unFollowLeadsNum,c.newClienteleNum as newClienteleNum,d.signClienteleNum,e.backContractNum from ");
        querySql +=String.format("(SELECT count(1) as newLeadsNum FROM leads WHERE weekofyear(created)=weekofyear('%s') and staffId='%s') as a,",date,staffId);
        querySql +=String.format("(SELECT count(1) as unFollowLeadsNum FROM leads WHERE weekofyear(created)=weekofyear('%s') and lastFollowTime is NULL and staffId='%s') as b,",date,staffId);
        querySql +=String.format("(SELECT count(1) as newClienteleNum FROM clientele WHERE weekofyear(created)=weekofyear('%s') and staffId='%s') as c,",date,staffId);
        querySql +=String.format("(SELECT count(DISTINCT(cl.id)) as signClienteleNum from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE weekofyear(con.created)=weekofyear('%s') and weekofyear(cl.created)=weekofyear('%s') and con.status !=2 and cl.staffId='%s') as d,",date,date,staffId);
        querySql +=String.format("(SELECT count(1) as backContractNum from contract WHERE date(created)<date('%s') and status!=2 and payStatus!=1 and staffId='%s') as e",date,staffId);
        List<WeekPaperAdviserVo> list = sqlObjectList(querySql,WeekPaperAdviserVo.class);
        return list.size() >0 ? list.get(0) : null;
    }

    @Override
    public WeekPaperAreaVo getAreaWeekData(String areaId,String date) {
        String querySql = String.format(" select a.newLeadsNum as newLeadsNum,b.initiativeLeadsNum as initiativeLeadsNum,c.passivityLeadsNum as passivityLeadsNum,d.leadsFollowNum as leadsFollowNum,e.newClienteleNum as newClienteleNum,f.clienteleFollowNum as clienteleFollowNum,g.signClienteleNum as signClienteleNum,h.backClienteleNum as backClienteleNum from ");
        //新线索数
        querySql +=String.format("(SELECT count(1) as newLeadsNum FROM leads WHERE weekofyear(created)=weekofyear('%s') and areaId='%s') as a ,",date,areaId);
        //主动线索数
        querySql +=String.format("(SELECT count(1) as initiativeLeadsNum FROM leads WHERE weekofyear(created)=weekofyear('%s') and category=1 and areaId='%s') as b ,",date,areaId);
        //被动线索数
        querySql +=String.format("(SELECT count(1) as passivityLeadsNum FROM leads WHERE weekofyear(created)=weekofyear('%s') and category=2 and areaId='%s') as c ,",date,areaId);
        //线索跟进数
        querySql +=String.format("(SELECT count(1) as leadsFollowNum from leads_history WHERE weekofyear(created)=weekofyear('%s') and type='leads' and  operationType='follow' and areaId='%s') as d ,",date,areaId);
        //新客户数
        querySql +=String.format("(SELECT count(1) as newClienteleNum FROM clientele WHERE weekofyear(created)=weekofyear('%s') and areaId='%s') as e ,",date,areaId);
        //客户跟进数
        querySql +=String.format("(SELECT count(1) as clienteleFollowNum from leads_history WHERE weekofyear(created)=weekofyear('%s') and type='clientele' and operationType='follow' and areaId='%s') as f ,",date,areaId);
        //签约客户
        querySql +=String.format("(SELECT count(DISTINCT(cl.id)) as signClienteleNum from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId WHERE weekofyear(con.created)=weekofyear('%s') and weekofyear(cl.created)=weekofyear('%s') and con.status !=2 and cl.areaId='%s') as g ,",date,date,areaId);
        //待回款客户
        querySql +=String.format("(SELECT count(DISTINCT(clienteleId)) as backClienteleNum from contract WHERE date(created)<date('%s') and status!=2 and payStatus!=1 and areaId='%s') as h",date,areaId);
        //优秀顾问Id
        //querySql +=String.format("() as l");
        List<WeekPaperAreaVo> list = sqlObjectList(querySql,WeekPaperAreaVo.class);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int getTotalNumByStaffId(String staffId,String date) {
        String querySql = String.format("SELECT SUM(a.newLeadsNum*3+b.followNum*4+c.signClienteleNum*10) as totalNum from ");
        querySql +=String.format("(SELECT count(1) as newLeadsNum,staffId as staffId FROM leads WHERE  weekofyear(created)=weekofyear('%s') AND staffId='%s' )as a,",date,staffId);
        querySql +=String.format("(SELECT count(1) as followNum,staffId as staffId  from leads_history WHERE weekofyear(created)=weekofyear('%s') and operationType='follow' AND staffId='%s') as  b,",date,staffId);
        querySql +=String.format("(SELECT count(DISTINCT(cl.id)) as signClienteleNum,cl.staffId from clientele as cl INNER JOIN contract as con ON cl.id=con.clienteleId " +
                " WHERE weekofyear(con.created)=weekofyear('%s') and weekofyear(cl.created)=weekofyear('%s') and con.status !=2 and cl.staffId='%s' )as c",date,date,staffId);
        List list = sqlSingleList(querySql);
        if (list.size() > 0)
            return list.get(0) == null ? 0 : Integer.parseInt(list.get(0).toString());
        else
            return 0;
    }
}
